"""
Case Type   : python驱动psycopg2
Case Name   : ssl(服务器)=on,sslmode(客户端)=disable,require_ssl(服务器)=on,无法建立ssl连接
Create At   : 2023/9/21
Owner       : @ningyali
Description :
    1. 解压PDBC得到lib,替换psycopg2
    2. 配置lib
    3. 开启SSL认证模式
    4. 配置客户端接入认证参数，IP为所要连接的主机IP
    5. 配置SSL认证相关的数字证书参数
    6. 将create_ca脚本发送到数据库主机
    7. 通过create_ca脚本生成证书
    8. 替换证书
    9. 将生成证书打包成db-cert-replacement.zip发送到需要连接数据库的主机并解压
    10. ssl连接
Expect      :
    1.拷贝成功
    2.配置成功
    3.执行成功
    4.配置成功
    5.配置成功
    6.执行成功
    7.执行成功
    8.替换成功
    9.执行成功
    10.连接无法建立
History     :
"""
import os
import subprocess
import unittest

from yat.test import macro

from testcase.CONNECTORS.PDBC.PSYCOPG2. \
    Opengauss_Function_Connect_Psycopg_Common import Psycopg2Common
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Psycopg9(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.Constant = Constant()
        self.Common = Common()
        self.conn_info = {'dbname': self.pri_sh.node.db_name,
                          'user': self.pri_sh.node.db_user,
                          'password': self.pri_sh.node.db_password,
                          'host': self.pri_sh.node.ssh_host,
                          'port': self.pri_sh.node.db_port}
        self.psycopg_com = Psycopg2Common(**self.conn_info)
        self.script_name = 'create_ca.sh'
        self.remote_path = os.path.join('/home', self.pri_sh.node.ssh_user)
        self.zip_name = 'db-cert-replacement.zip'

        text = '----前置操作: 备份白名单 expect: 备份成功----'
        self.log.info(text)
        res = self.psycopg_com.bak_conf(bak_flag=True)
        self.log.info(res)
        self.assertTrue(res, f'执行失败: {text}')

        text = '----前置操作: 备份配置文件 expect: 备份成功----'
        self.log.info(text)
        res = self.psycopg_com.bak_conf(bak_flag=True,
                                        f_name=macro.DB_PG_CONFIG_NAME)
        self.log.info(res)
        self.assertTrue(res, f'执行失败: {text}')

    def test_1(self):
        text = '----step1: 解压PDBC得到lib,替换psycopg2 expect: 执行成功----'
        res = self.psycopg_com.decompress_pkg()
        self.assertTrue(res, f'执行失败: {text}')
        res = self.psycopg_com.set_new_psycopg2()
        self.assertTrue(res, f'执行失败: {text}')

        text = '----step2: 配置lib expect: 成功----'
        self.log.info(text)
        res = self.psycopg_com.set_lib_path()
        self.assertTrue(res, f'执行失败: {text}')

        text = '----step3: 开启SSL认证模式 expect: 成功----'
        self.log.info(text)
        for param in ('ssl=on', 'require_ssl=on'):
            res = self.pri_sh.execute_gsguc('set',
                                            self.Constant.GSGUC_SUCCESS_MSG,
                                            param,
                                            single=True)
            self.assertTrue(res, f'执行失败: {text}')
        self.log.info('为减少用例执行时间，此处不重启，第8步重启生效')

        text = '----step4: 配置客户端接入认证参数，IP为所要连接的主机IP expect: 成功----'
        self.log.info(text)
        res = self.psycopg_com.set_pghba(open_flag=True,
                                         mode='cert')
        self.assertTrue(res, f'执行失败: {text}')

        text = '----step5: 配置SSL认证相关的数字证书参数 expect: 成功----'
        self.log.info(text)
        for param in ("ssl_cert_file='server.crt'",
                      "ssl_key_file='server.key'",
                      "ssl_ca_file='cacert.pem'"):
            res = self.pri_sh.execute_gsguc('set',
                                            self.Constant.GSGUC_SUCCESS_MSG,
                                            param,
                                            single=True)
            self.assertTrue(res, f'执行失败: {text}')
        self.log.info('为减少用例执行时间，此处不重启，第8步重启生效')

        text = '----step6: 将create_ca脚本发送到数据库主机 expect: 成功----'
        self.log.info(text)
        self.Common.scp_file(self.pri_sh.node, self.script_name,
                             self.remote_path)
        res = self.pri_sh.node.sh(
            f'ls {self.remote_path}|grep {self.script_name}').result()
        self.log.info(res)
        self.assertTrue(res.strip(), f'执行失败: {text}')

        text = '----step7: 通过create_ca脚本生成证书 expect: 成功----'
        self.log.info(text)
        sh_cmd = f'source {macro.DB_ENV_PATH};' \
            f'sh {self.script_name} {self.pri_sh.node.ssh_user} ' \
            f'{self.pri_sh.node.ssh_password} {self.pri_sh.node.db_user}'
        res = self.pri_sh.node.sh(sh_cmd).result()
        self.log.info(res)
        res = self.pri_sh.node.sh(
            f'ls {self.remote_path}|grep {self.zip_name}').result()
        self.log.info(res)
        self.assertTrue(res.strip(), f'执行失败: {text}')

        text = '----step8: 替换证书  expect: 成功----'
        self.log.info(text)
        zip_path = os.path.join(self.remote_path, self.zip_name)
        replace_cmd = f'source {macro.DB_ENV_PATH};' \
            f'gs_om -t cert --cert-file={zip_path}'
        self.log.info(replace_cmd)
        replace_res = self.pri_sh.node.sh(replace_cmd).result()
        self.log.info(replace_res)
        self.assertIn('Successfully replace om ssl cert files', replace_res,
                      f'执行失败: {text}')
        self.assertIn('Successfully distributed cert files on all nodes',
                      replace_res, f'执行失败: {text}')

        is_started = self.pri_sh.restart_db_cluster()
        self.assertTrue(is_started, f'执行失败: {text}')

        text = '----step9: 将生成证书打包成db-cert-replacement.zip发送到' \
               '需要连接数据库的主机并解压 expect: 成功----'
        self.log.info(text)
        spawn_cmd = f"spawn scp -r {self.pri_sh.node.ssh_user}@" \
            f"{self.pri_sh.node.ssh_host}:{zip_path} ."
        scp_cmd = f'''expect <<EOF
            set timeout 300
            {spawn_cmd}
            expect {{
                "*yes/no*" {{ send "yes\\n"; exp_continue }}
                "*assword:" {{ send "{self.pri_sh.node.ssh_password}\\n" }}
                "]#" {{ send "\\n" }}
            }}
            expect eof\n''' + 'EOF'
        self.log.info(scp_cmd)
        scp_res = subprocess.getoutput(scp_cmd)
        self.log.info(scp_res)
        self.assertIn('100%', scp_res, f'执行失败: {text}')

        tar_res = subprocess.getoutput(f'unzip {self.zip_name}')
        self.log.info(tar_res)
        self.assertGreater(tar_res.count('inflating'), 2, f'执行失败: {text}')
        self.assertGreater(tar_res.count('extracting'), 2, f'执行失败: {text}')

        text = '----step10: ssl连接 expect: 连接无法建立----'
        self.log.info(text)
        res = self.psycopg_com.conn_db(by="ssl", cert_path=os.getcwd(),
                                       ssl_mode='disable')
        expect = 'psycopg2.OperationalError: FATAL:  SSL connection is ' \
                 'required by the database system'
        self.assertIn(expect, res, f'执行失败: {text}')

    def tearDown(self):
        text = '----run teardown----'
        self.log.info(text)

        text1 = f'----后置操作：删除解压文件目录----'
        self.log.info(text1)
        res1 = self.psycopg_com.clean_decompress_file()

        text2 = f'----后置操作：还原白名单----'
        self.log.info(text2)
        res2 = self.psycopg_com.bak_conf(bak_flag=False)

        text3 = f'----后置操作：还原配置文件----'
        self.log.info(text3)
        res3 = self.psycopg_com.bak_conf(bak_flag=False,
                                         f_name=macro.DB_PG_CONFIG_NAME)

        text4 = f'----后置操作：删除生成证书----'
        self.log.info(text4)
        rm_cmd = f'rm -rf {self.script_name} {self.zip_name} cacert.pem ' \
            f'client.* server.* demoCA openssl.cnf'
        res4 = self.pri_sh.node.sh(rm_cmd).result()
        self.log.info(res4)
        rm_cmd = rm_cmd.replace(self.script_name, '')
        res5 = subprocess.getoutput(rm_cmd)
        self.log.info(res5)

        self.assertTrue(res1, f'执行失败: {text1}')
        self.assertTrue(res2, f'执行失败: {text2}')
        self.assertTrue(res3, f'执行失败: {text3}')
        self.assertEqual(len(res4.strip()), 0, f'执行失败: {text4}')
        self.assertEqual(len(res5.strip()), 0, f'执行失败: {text4}')

        text = f'-----{os.path.basename(__file__)} end-----'
        self.log.info(text)
